import openpyxl
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter
import numpy as np
import os



# xlsx_path = "/root/family/python/python_xlsx/test.xlsx"
xlsx_path = "/root/family/python/python_xlsx/data_metal.xlsx"
txt_path = "/root/family/python/python_xlsx/data_metal.txt"
# depth_dict = {"0-0.5": 0, "0.5-1": 1, "0.5-1.0": 1, "1.0-1.5": 2, "1-1.5": 2, "1.5-2": 3, "1.5-2.0": 3,
#           "2-2.5": 4, "2.0-2.5": 4, "2.5-3": 5, "2.5-3.0": 5, "3-4": 6, "3-4.0": 6, "4-5": 7, "4-4.5": 7, 
#           "5-6": 8, "6-7": 9, "7-8": 10}
depth_dict = {"0-0.5": 0, "0.5-1": 1, "1-1.5": 2, "1.5-2": 3,
              "2-2.5": 4, "2.5-3": 5,  "3-4": 6,  "4-5": 7, 
              "5-6": 8, "6-7": 9, "7-8": 10}


wb = openpyxl.load_workbook(xlsx_path)
sheet = wb['Sheet1']

# cell = sheet['B']
# 或
# cell = sheet.iter_cols(min_row=2, max_row=sheet.max_row, min_col=2, max_col=2)
# for row in cell:
#     for i, col in enumerate(row):
#         # 修改第二列的值
#         print(i)
#         sheet.cell(i+2, 2).value = depth_dict[col.value]

# wb.save(xlsx_path)

# 验证
# for row in cell:
#     for col in row:
#         print(col.value)

# print(type(sheet.cell(2, 2).value))



# # 获取第一行
# cell = sheet.iter_rows(min_row=1, max_row=1, min_col=1, max_col=sheet.max_column)

# for i in cell:
#     for j in i:
#         print('"' + str(j.value) + '"' , end=",")
#     print()
# 根据第一行得到金属的列表
icp_list = ["ICP-Cr","ICP-Ni","ICP-As","ICP-Sb","ICP-Cu","ICP-Pb","ICP-Zn","ICP-Cd"]
xrf_list = ["XRF-Cr","XRF-Ni","XRF-As","XRF-Sb","XRF-Cu","XRF-Pb","XRF-Zn","XRF-Cd"]





# 查看第一列的值，从第二行开始
# cell = sheet.iter_cols(min_row=2, max_row=sheet.max_row, min_col=1, max_col=1)
# for row in cell:
#     for col in row:
#         print(col.value)

# # # 根据第一列获取点位数据, 可以看到合并单元仅获取左上角的值，其余使用None表示
# # 2
# # None
# # None
# # None
# # None
# # None
# # None
# # 4
# # None
# # None
# # None
# # None
# # None
# # None
# # None
# # None
# # None
# # ...


def point_location_value_depth_num():
    """
    获取第一列中点位的值和点位所对应的深度数
    """

    point_location_list = []
    depth_num_list = []
    cell = sheet.iter_cols(min_row=2, max_row=sheet.max_row, min_col=1, max_col=1)
    # col_value_init = 0
    # 获取所有点位的值
    for row in cell:
        for col in row:
            col_value = col.value
            if col_value != None:
                col_value_init = col_value
            
            point_location_list.append(col_value_init)


    # print(point_location_list)
    # 获取点位的集合列表，也就是删去了重复的元素
    point_location_set_list = list(set(point_location_list))
    # print(point_location_set_list)
    # 获取点位对应
    for pl in point_location_set_list:
        depth_num = point_location_list.count(pl)
        depth_num_list.append(depth_num)

    # print(depth_num_list)

    return point_location_set_list, depth_num_list


# point_location_value_depth_num()

point_location_set_list = [2, 4, 10, 11, 16, 17, 18, 19, 20, 25, 26, 27, 28, 29, 30, 31, 34, 36, 37, 38, 39, 41, 42, 43, 44, 45, 47, 48, 50, 51, 52, 53]
depth_num_list = [7, 10, 5, 6, 7, 7, 3, 5, 7, 1, 6, 2, 4, 4, 8, 6, 7, 8, 8, 8, 8, 6, 3, 3, 3, 7, 7, 5, 6, 4, 1, 3]


# 获取所有的合并信息
# merge_coord_lists = sheet.merged_cells
# print(merge_coord_lists)
# # A66不知道为什么会在A2的前面，其余的都是按照数字排列的
# # 这样获取合并的信息显然不妥
# # A66:A67 A2:A8 A9:A18 A19:A23 A24:A29 A30:A36 A37:A43 A44:A46 A47:A51 A52:A58 A60:A65 A138:A140 A68:A71 A72:A75 A76:A83 A84:A89 A90:A96 A97:A104 A105:A112 A113:A120 A121:A128 A129:A134 A135:A137 A174:A176 A141:A143 A144:A150 A151:A157 A158:A162 A163:A168 A169:A172



def get_metal_point_location_depth_value(icp_list, xrf_list, point_location_set_list, depth_num_list):
    "将数据按照深度进行排列，同时将同一个金属的ICP与XRF放在一块"
    depth_num_max =  max(depth_num_list)
    depth_list = np.arange(depth_num_max)
    # [0 1 2 3 4 5 6 7 8 9 10]
    depth_metal_value_list = []
    # depth_metal_value_list: 设定的形态
    # [[depth, point_location, icp-cr, xrf-cd, ... icp-cd, xrf-cd], [], ..., []]
    for i in range(depth_num_max+1):
        depth_metal_value_list.append([])
    # print(depth_metal_value_list)
    # # [[], [], [], [], [], [], [], [], [], [], []]
    point_location_index = 0
    # 某个点位的深度对应在xlsx文件里面的行数，初始为2
    depth_row = 2
    for depth_num in depth_num_list:
        point_location_value = point_location_set_list[point_location_index]
        for i in range(depth_num):
            depth_coord = 'B' + str(depth_row)
            depth_value = sheet[depth_coord].value
            # print(depth_value)
            # 金属指标ICP-Cr到Cd对应的列为C-J
            # 金属指标XRF-Cr到Cd对应的列为L-S
            icp_metal_coord = 'C' + str(depth_row) + ':' + 'J' + str(depth_row)
            xrf_metal_coord = 'L' + str(depth_row) + ':' + 'S' + str(depth_row)
            cell_icp_metal = sheet[icp_metal_coord]
            cell_xrf_metal = sheet[xrf_metal_coord]
            depth_metal_value_list[depth_value].append(point_location_value)
            for icp_metals, xrf_metals in zip(cell_icp_metal, cell_xrf_metal):
                for icp_metal, xrf_metal in zip(icp_metals, xrf_metals):
                    icp_metal_value = icp_metal.value
                    xrf_metal_value = xrf_metal.value
                    depth_metal_value_list[depth_value].append(icp_metal_value)
                    depth_metal_value_list[depth_value].append(xrf_metal_value)

            depth_row += 1

        point_location_index += 1


    depth_metal_value_list = np.array(depth_metal_value_list)
    # print(depth_metal_value_list.shape) # (11, )
    # print(depth_metal_value_list[0]) # 对应深度0所对应的点位和指标值

    return depth_metal_value_list


def mental_deep_to_sheet(icp_list, xrf_list, point_location_set_list, depth_num_list, dst_xlsx_path):
    """
    将数据按照深度导出到xlsx文件中，每个深度的数据设定一个以深度命名的sheet,
    数据按照同一个金属的icp, xrf形式排列
    """
    depth_metal_value_list = get_metal_point_location_depth_value(icp_list, xrf_list, point_location_set_list, depth_num_list)
    depth_num_max = max(depth_num_list)
    sheet_first_row = ['point location']
    for icp, xrf in zip(icp_list, xrf_list):
        sheet_first_row.append(icp)
        sheet_first_row.append(xrf)
    column_len = len(sheet_first_row)
    # 将整理的数据写入新的xlsx文件中，sheet1写入深度为0的信息，以此类推
    # 创建一个工作薄对象
    wb_write = openpyxl.Workbook()
    # 在索引为i的位置创建一个名为depth的sheet页
    # ws = wb_write.create_sheet('depth', i)
    index = 0
    depth_num = 0
    for i in range(depth_num_max+1):
        # 第i个深度对应的数据
        depth_metal_value_list_i = depth_metal_value_list[i]
        # 数据的行数
        row_len = len(depth_metal_value_list_i) // column_len
        if depth_metal_value_list_i != []:
            sheet_name = 'depth' + str(depth_num)
            ws = wb_write.create_sheet(sheet_name, index)
            # 设定第一列宽度为20
            ws.column_dimensions['A'].width = 20
            for j in range(column_len):
                # 将第一行设定的内容写入，总计column_len=17列
                # 注意column这里是从1开始计数的
                ws.cell(row=1, column=j+1).value = sheet_first_row[j]
                # 第一行居中对齐
                ws.cell(row=1, column=j+1).alignment = Alignment(horizontal='center', vertical='center')
                # # 设定行的高度，row_letter使用数字表示即可，例如1
                # ws.row_dimensions[row_letter].height= 20
                # 设定列的宽度
                # col_letter不能是索引数字，只能是字符串，像'A'这样的
                # 这里使用函数get_column_letter进行自动转换, 1 -> 'A'
                # 设定第二列以及后面的宽度为10
                col_letter = get_column_letter(j+2)
                ws.column_dimensions[col_letter].width = 10
                # 从第二行开始输入整理的数据内容
                for k in range(row_len):
                    ws.cell(row=k+2, column=j+1).value = depth_metal_value_list_i[column_len*k+j]
                    # 第二行开始往后居中对齐,
                    ws.cell(row=k+2, column=j+1).alignment = Alignment(horizontal='center', vertical='center')

            index += 1
        depth_num += 1

    wb_write.save(dst_xlsx_path)
    wb_write.close()



# 修改文件所在的目录为当前的工作目录
root = os.path.dirname(__file__)
os.chdir(root)
dst_xlsx_path = "./data_metal_output.xlsx"
# get_metal_point_location_depth_value(icp_list, xrf_list, point_location_set_list, depth_num_list)
# mental_deep_to_sheet(icp_list, xrf_list, point_location_set_list, depth_num_list, dst_xlsx_path)

def mental_deep_to_xlsx():
    """
    将数据按照先深度的顺序后点位的顺序导出到同一个sheet表格中，
    数据按照不同金属的icp和xrf依次排列
    暂未修改
    """
    depth_metal_value_list = get_metal_point_location_depth_value(icp_list, xrf_list, point_location_set_list, depth_num_list)
    depth_num_max = max(depth_num_list)
    sheet_first_row = ['point location']
    for icp, xrf in zip(icp_list, xrf_list):
        sheet_first_row.append(icp)
        sheet_first_row.append(xrf)
    column_len = len(sheet_first_row)
    # 将整理的数据写入新的xlsx文件中，sheet1写入深度为0的信息，以此类推
    # 创建一个工作薄对象
    wb_write = openpyxl.Workbook()
    # 在索引为i的位置创建一个名为depth的sheet页
    # ws = wb_write.create_sheet('depth', i)
    index = 0
    depth_num = 0
    for i in range(depth_num_max+1):
        # 第i个深度对应的数据
        depth_metal_value_list_i = depth_metal_value_list[i]
        # 数据的行数
        row_len = len(depth_metal_value_list_i) // column_len
        if depth_metal_value_list_i != []:
            sheet_name = 'depth' + str(depth_num)
            ws = wb_write.create_sheet(sheet_name, index)
            # 设定第一列宽度为20
            ws.column_dimensions['A'].width = 20
            for j in range(column_len):
                # 将第一行设定的内容写入，总计column_len=17列
                # 注意column这里是从1开始计数的
                ws.cell(row=1, column=j+1).value = sheet_first_row[j]
                # 第一行居中对齐
                ws.cell(row=1, column=j+1).alignment = Alignment(horizontal='center', vertical='center')
                # # 设定行的高度，row_letter使用数字表示即可，例如1
                # ws.row_dimensions[row_letter].height= 20
                # 设定列的宽度
                # col_letter不能是索引数字，只能是字符串，像'A'这样的
                # 这里使用函数get_column_letter进行自动转换, 1 -> 'A'
                # 设定第二列以及后面的宽度为10
                col_letter = get_column_letter(j+2)
                ws.column_dimensions[col_letter].width = 10
                # 从第二行开始输入整理的数据内容
                for k in range(row_len):
                    ws.cell(row=k+2, column=j+1).value = depth_metal_value_list_i[column_len*k+j]
                    # 第二行开始往后居中对齐,
                    ws.cell(row=k+2, column=j+1).alignment = Alignment(horizontal='center', vertical='center')

            index += 1
        depth_num += 1

    wb_write.save(dst_xlsx_path)
    wb_write.close()


# def merge_depth_point_location(icp_list, xrf_list, point_location_set_list, depth_num_list, xlsx_path):
#     """
#     使用depth--point_location的方式整理数据, ICP和XRF数据然后依次按列排列，
#     命名为depth_point_location
#     """
#     depth_metal_value_list = get_metal_point_location_depth_value(icp_list, xrf_list, 
#                                                                   point_location_set_list, 
#                                                                   depth_num_list)


# def get_data(txt_path):
#     f = open(txt_path, "r")
#     f_r = f.readlines()
#     for data in f_r:
#         mental_data = data.strip("\n").split("\t")


def get_data(xlsx_path):
    wb = openpyxl.load_workbook(xlsx_path)
    sheet = wb['Sheet1']   
    nrows = sheet.max_row # 176
    ncols = sheet.max_column # 18
    cell = sheet.iter_rows(min_row=2, max_row=nrows, min_col=3, max_col=ncols)
    # 输出简单的实例: 
    # cell = sheet.iter_rows(min_row=2, max_row=5, min_col=3, max_col=6)
    data_list = []
    for i, row in enumerate(cell):
        for j in row:
            data_list.append(j.value)
    data_array = np.array(data_list).reshape(-1, ncols-2)
    output_data, input_data = np.hsplit(data_array, 2)

    # # print(data_array)
    # # print(output_data)
    # # print(input_data)
    # [[ 85.7  44.6 596.  155. ]
    # [ 67.6  35.3 235.   81.1]
    # [ 62.8  36.2 268.   64.8]
    # [ 57.3  40.1  86.3  16.8]]

    # [[85.7 44.6]
    # [67.6 35.3]
    # [62.8 36.2]
    # [57.3 40.1]]
    
    # [[596.  155. ]
    # [235.   81.1]
    # [268.   64.8]
    # [ 86.3  16.8]]

    return input_data, output_data

xlsx_path = "/root/family/python/python_xlsx/data_metal.xlsx"
# get_data(xlsx_path)






